topCombinations <- function(con, tblname, col="Median", entry="o1", ratio=FALSE, dir="b",
                            top=20, minobs="auto", fullTbl=FALSE, OOSComparison=FALSE, addSignal=TRUE, latex=FALSE) {
  if('package:RMySQL' %in% search() || require('RMySQL',quietly=TRUE)) {}
  if (latex==TRUE) {
    if('package:xtable' %in% search() || require('xtable',quietly=TRUE)) {}
  }
  
  tblname_an <- paste(tblname, "_an", sep="")
  sqlcmd <- function(dir) {
    sql <- sprintf("SELECT * FROM `%s` WHERE `obs` > '%d' ORDER BY `d_%s_%s_%s` DESC", tblname_an, minobs, entry, dir, col)
    return (sql)
  }
  sqlcmdcount <- function(tblname) {
    sql <- sprintf("SELECT COUNT(*) AS cnt FROM `%s`", tblname)
    return (sql)
  }
  sqlcmdratio <- function(dir) {
    if (dir=="b") {a="b"; b="s"} else {a="s"; b="b"}
    sql <- sprintf("SELECT * FROM `%s` WHERE `obs` > '%d' ORDER BY 
                   (`d_%s_%s_%s`-`d_%s_%s_%s`)/(`d_%s_b_%s`+`d_%s_s_%s`) DESC",tblname_an, minobs,
                   entry,a,col, entry,b,col, entry,col, entry,col)
    return(sql)
  }
  sqlcmdId <- function(table, id) {
    sql <- sprintf("SELECT `obs`,`%s`,`%s` FROM `%s` WHERE `id` = '%d'",bcol, scol, table, id)
    return(sql)
  }
  
  if (minobs=="auto") {
    rowcount <- dbGetQuery(con, sqlcmdcount(tblname))
    minobs <- ceiling(rowcount[1,1]/1000)
  }
  if (ratio==FALSE) {
    btbl <- dbGetQuery(con, sqlcmd(dir))    
  } else {
    btbl <- dbGetQuery(con, sqlcmdratio(dir))
  }
  bcol = sprintf("d_%s_b_%s", entry, col)
  scol = sprintf("d_%s_s_%s", entry, col)
  ratio <- 100*(btbl[,bcol] - btbl[,scol])/(btbl[,bcol] + btbl[,scol])
  result <- cbind(btbl, ratio)
  if (fullTbl==TRUE) {
    result <- result[1:top,]
  } else {
    dcols <- cbind(result[,bcol], result[,scol])
    colnames(dcols) <- c(substr(bcol,3,6), substr(scol,3,6))
    selection <- cbind(result[,1:2], dcols, ratio)
    result <- selection[1:top,]
  }
  
  # form text row with all binary signals that are set
  cols <- dbListFields(con, tblname_an)
  bcols <- cols[grep("^b_", cols)]
  lbcols <- length(bcols)
  filterstr <- matrix("", nrow=top, ncol=1)
  for (i in 1:top) {
    for (j in 1:lbcols) {
      if (btbl[i,bcols[j]] == TRUE) 
        filterstr[i,1] <- paste(filterstr[i,1], substr(bcols[j], 3, nchar(bcols[j])))
    }
  }
  filterstr <- as.data.frame(filterstr)
  colnames(filterstr) <- c("signals")
  
  if (OOSComparison==FALSE) {
    if (addSignal==TRUE) result <- cbind(result, filterstr)   
    align="r|r|r|ccc|l|"
    display=c("d","d","d","f","f","f","s")
  } else {
    align="r|r|r|ccc|r|ccc|l|"
    display=c("d", "d", "d","f","f","f", "d","f","f","f","s")
    dimres <- dim(result)
    mat <- matrix(0, nrow=dimres[1], ncol=4)
    for (i in 1:(dimres[1])) {
      OOSrow <- dbGetQuery(con, sqlcmdId(paste(tblname, "_OOS_an", sep=""), result[i,"id"]))
      if (dim(OOSrow)[1] > 0) {
        mat[i,1] <- OOSrow[1,"obs"]
        mat[i,2] <- OOSrow[1,bcol]
        mat[i,3] <- OOSrow[1,scol]
        mat[i,4] <- 100*(OOSrow[1,bcol] - OOSrow[1,scol])/(OOSrow[1,bcol] + OOSrow[1,scol])
      }
    }
    OOSDf <- as.data.frame(mat)
    colnames(OOSDf) <- c("obs", substr(bcol,3,6), substr(scol,3,6), "ratio")
    result <- cbind(result, OOSDf)
    if (addSignal==TRUE) result <- cbind(result, filterstr)    
  }
  if (latex==TRUE) {
    title <- gsub("_", "-", sprintf("StockStatus group:%s. entry:%s. minobs:%d. dir:%s.", tblname, entry, minobs, dir), fixed=TRUE)
    label <- sprintf("tab:Simulation:%s.%s.%d.%s", tblname, entry, minobs, dir)
    xt <- xtable(result, caption=title, label=label, align=align, display=display)
    print(xt, include.rownames=FALSE)
  } else {
    return(result)
  }
}

allTopCombinations <- function(tblprefix) {
  con <- connectToVadb()
  groups <- groupsToUse()
  for (group in groups) {
    topCombinations(con, paste(tblprefix, group, sep="_"), ratio=TRUE, OOSComparison=TRUE, dir="b", latex=TRUE)
    topCombinations(con, paste(tblprefix, group, sep="_"), ratio=TRUE, OOSComparison=TRUE, dir="s", latex=TRUE)
  }
  disconnectFromVadb(con)
}

calcRatio <- function(bcol, scol) {
  ratio <- 100*(bcol - scol)/(bcol + scol)
  return (ratio)
}

# dummy function to return the current groups in va_stratlab database
groupsToUse <- function () {
  return (c("INDEX_MAJORS", "DJI_STOCKS", "SMALL_CAPS", "PM_STOCKS", "TECH_STOCKS"))
}

# examines the performance of a given id on all test groups
SSIntermarketCheck <- function(con, tblprefix, id, col="Median", latex=FALSE) {
  if (latex==TRUE) {
    if('package:xtable' %in% search() || require('xtable',quietly=TRUE)) {}
  }
  entryCodes <- c("c0", "o1", "c1")
  sqlcmdId <- function(table, id, bcol, scol) {
    sql <- sprintf("SELECT `obs`,`%s`,`%s` FROM `%s` WHERE `id` = '%d'",bcol, scol, table, id)
    return(sql)
  }
  groups <- groupsToUse()
  tblcount <- 1
  stratstr <- NULL
  while (is.null(stratstr) & tblcount <= length(groups)) {
    stratstr <- stringFromId(con, tblprefix, groups[tblcount], id)
    tblcount <- tblcount+1
  }

  mat <- matrix(0, nrow=length(groups)*2, ncol=10)
  rowcount <- 1
  rown <- c()
  for (group in groups) {
    colcount <- 2
    for (entry in entryCodes) {
      bcol = sprintf("d_%s_b_%s", entry, col)
      scol = sprintf("d_%s_s_%s", entry, col)
      ISrow <- dbGetQuery(con, sqlcmdId(paste(tblprefix, group, "an", sep="_"), id, bcol, scol)) 
      OOSrow <- dbGetQuery(con, sqlcmdId(paste(tblprefix, group, "OOS_an", sep="_"), id, bcol, scol))
      # fill matrix with values
      if (dim(ISrow)[1]>0) {
        mat[rowcount, 1] <- ISrow[1,"obs"]        
        mat[rowcount, colcount] <- ISrow[1,bcol]
        mat[rowcount, colcount+1] <- ISrow[1,scol]
        mat[rowcount, colcount+2] <- calcRatio(ISrow[1,bcol], ISrow[1,scol])
      }
      if (dim(OOSrow)[1]>0) {
        mat[rowcount+1, 1] <- OOSrow[1,"obs"]
        mat[rowcount+1, colcount] <- OOSrow[1,bcol]
        mat[rowcount+1, colcount+1] <- OOSrow[1,scol]
        mat[rowcount+1, colcount+2] <- calcRatio(OOSrow[1,bcol], OOSrow[1,scol])      
      }
      colcount <- colcount + 3
    }
    rown <- c(rown, group, paste(group, "OOS", sep="_"))
    rowcount <- rowcount + 2
  }
  df <- as.data.frame(mat)
  colnames(df) <- c("obs", "c0_b", "c0_s", "c0_ratio",   "o1_b", "o1_s", "o1_ratio",  "c1_b", "c1_s", "c1_ratio" )
  rownames(df) <- rown
  
  if (latex==FALSE) {
    result<- list()
    result[[1]]<-df
    result[[2]]<-stratstr
    return (result)    
  } else {
    title <- sprintf("IntermarketCheck for %s, id %s", tblprefix, id)
    label <- paste(tblprefix, id, sep="_")
    align="|r|r|ccc|ccc|ccc|"
    display=c("s","d", "f","f","f", "f","f","f", "f","f","f")
    xt <- xtable(df, caption=title, label=label, align=align, display=display)
    print(xt, include.rownames=TRUE)
  }
}

# this function is to analyze the output of the function SSAllIntermarketCheckIDs
SSAllICTop <- function(df, tblprefix,entry="o1", min=10, max=2, latex=FALSE){
  buycol <- paste(entry, "b", sep="_")
  sellcol <- paste(entry, "s", sep="_")
  df <- df[,1:7]
  result <- df[df[,buycol]>=min & df[,sellcol]<=max, ]
  result <- rbind(result, df[df[,sellcol]>=min & df[,buycol]<=max, ])
  signals <- matrix(0, nrow=dim(result)[1], ncol=1)
  for (i in 1:dim(result)[1]) {
    signals[i,1] <- stringFromId(con, tblprefix, "DJI_STOCKS", result[i,"id"])
  }
  if (latex==TRUE) {
    if('package:xtable' %in% search() || require('xtable',quietly=TRUE)) {}
    title <- gsub("_", "-", sprintf("Signals in all test markets: %s.", tblprefix), fixed=TRUE)
    label <- sprintf("tab:Signals:%s", tblprefix)
    align="r|r|cccccc|l|"
    display=c("d", "d", "d","d","d","d","d","d", "s")
    xt <- xtable(cbind(result, as.data.frame(signals)), caption=title, label=label, align=align, display=display)
    print(xt, include.rownames=FALSE)
  } else {
    return(cbind(result, as.data.frame(signals)))    
  }  
}

# further analyze the return variable with SSAllICTop
SSAllIntermarketCheckIDs <- function(con, tblprefix, col="Median") {
  sqlcmdDistinct <- function(table) {
    sql <- sprintf("SELECT DISTINCT `id` FROM `%s`",table)
    return(sql)
  }
  groups <- groupsToUse()
  ids <- c()
  # gather all ids from all datasets
  for (group in groups) {
    ISids <- dbGetQuery(con, sqlcmdDistinct(paste(tblprefix, group, "an", sep="_")))
    OOSids <- dbGetQuery(con, sqlcmdDistinct(paste(tblprefix, group, "OOS_an", sep="_")))
    ids <- c(ids, ISids[,1][!(ISids[,1] %in% ids)])
    ids <- c(ids, OOSids[,1][!(OOSids[,1] %in% ids)])    
  }
  ids <- sort(ids)
  
  mat <- matrix(0, nrow=length(ids), ncol=7)
  rowc <- 1
  for (id in ids) {
    dflist <- SSIntermarketCheck(con, tblprefix=tblprefix, id=id, col=col)
    df <- dflist[[1]]
    mat[rowc,1] <- id
    mat[rowc,2] <- length(df[,4][df[,4] >= 0])
    mat[rowc,3] <- length(df[,4][df[,4] <= 0])
    mat[rowc,4] <- length(df[,7][df[,7] >= 0])
    mat[rowc,5] <- length(df[,7][df[,7] <= 0])
    mat[rowc,6] <- length(df[,10][df[,10] >= 0])
    mat[rowc,7] <- length(df[,10][df[,10] <= 0])
    rowc <- rowc + 1
  }
  df <- as.data.frame(mat)
  colnames(df) <- c("id", "c0_b", "c0_s", "o1_b", "o1_s", "c1_b", "c1_s")
  return (df)
}

stringFromId <- function(con, tblprefix, groupcode, id) {
  sqlcmdId <- function(table, id) {
    sql <- sprintf("SELECT * FROM `%s` WHERE `id` = '%d'", table, id)
    return(sql)
  }  
  rowIS <- dbGetQuery(con, sqlcmdId(paste(tblprefix, groupcode, "an", sep="_"), id))
  rowOOS <- dbGetQuery(con, sqlcmdId(paste(tblprefix, groupcode, "OOS_an", sep="_"), id))
  if ((dim(rowIS)[1]) <1){
    row <- rowOOS
  } else {
    row <- rowIS
  }
  if ((dim(row)[1]) >0){
    bcols <- colnames(row)[grep("^b_", colnames(row))]
    lbcols <- length(bcols)
    filterstr <- ""
    lbcols <- length(bcols)
    for (i in 1:lbcols) {
      if (row[1,bcols[i]] == TRUE)
        filterstr <- paste(filterstr, substr(bcols[i], 3, nchar(bcols[i])))
    }
    return (filterstr)
  } else {
    return (FALSE)
  }
}